"""
@File    :   service.py
@Time    :   2024/03/27 12:38:50
@Author  :   Qrj
@Email   :   923207736@qq.com
@description   :   租户套餐相关业务逻辑
"""

from cashews import NOT_NONE
from sqlalchemy import and_, delete, func, insert, select, update
from sqlalchemy.ext.asyncio import AsyncSession

from src.business.admin.system.menu.models import Menu
from src.business.admin.system.menu.schemas.out import (
    ListMenuCompleteOutSchema,
    MenuCompleteOutSchema,
)
from src.business.admin.system.tenant.tenant.models import Tenant
from src.exceptions.custom_async_validator import generate_async_validator_exception
from src.schemas.validate import (
    OrderBySearchValidateSchema,
    PaginationSearchValidateSchema,
)
from src.utils.cashews import CashewsCondition, data_cache

from .models import TenantPackage, TenantPackageMenuRel
from .schemas.out import (
    ListTenantPackageCompleteOutSchema,
    ListTenantPackageSimpleOutSchema,
    TenantPackageAndMenuCompleteOutSchema,
    TenantPackageCompleteOutSchema,
    TenantPackageSimpleOutSchema,
)
from .schemas.validate import (
    TenantPackageCreateValidateSchema,
    TenantPackageDeleteValidateSchema,
    TenantPackageListSearchValidateSchema,
    TenantPackageUpdateStatusValidateSchema,
    TenantPackageUpdateValidateSchema,
)


class TenantPackageService:
    """租户套餐相关业务逻辑类"""

    @data_cache(
        ttl="30m",
        key="tenant_package#id:{tenant_package_id}",
        tags=["tenant_package#id", "tenant_package#id:{tenant_package_id}"],
        condition=NOT_NONE,
    )
    @staticmethod
    async def get_tenant_package_use_id(
        db_session: AsyncSession,
        tenant_package_id: int,
        raise_not_exists_exception: bool = True,
        raise_disabled_exception: bool = True,
    ) -> TenantPackageAndMenuCompleteOutSchema | None:
        """获取租户套餐及其菜单ID列表(主键ID)

        Args:
            db_session (AsyncSession): 数据库连接池
            tenant_package_id (int): 租户套餐ID
            raise_not_exists_exception (bool, optional): 不存在是否直接报错
            raise_disabled_exception (bool, optional): 被禁用是否直接报错

        Returns:
            TenantPackageAndMenuIdsCompleteOutSchema: 租户套餐信息
        """
        # 租户套餐信息查询sql
        search_sql = select(
            *TenantPackageAndMenuCompleteOutSchema.convert_to_sql_model_field(
                TenantPackage, exclude_field=("menu_ids", "menu_permission")
            )
        ).where(TenantPackage.id == tenant_package_id)
        # 租户套餐对应的菜单信息查询的sql
        search_rel_sql = (
            select(TenantPackageMenuRel.menu_id, Menu.permission)
            .select_from(TenantPackageMenuRel)
            .join(Menu, TenantPackageMenuRel.menu_id == Menu.id)
            .where(TenantPackageMenuRel.tenant_package_id == tenant_package_id)
        )
        async with db_session.begin():
            search_res = await db_session.execute(search_sql)
            search_rel_res = await db_session.execute(search_rel_sql)
        search_rel_res = search_rel_res.mappings().all()
        result = search_res.mappings().first()
        if result is not None:
            menu_ids = [i["menu_id"] for i in search_rel_res]
            menu_permission = [
                i["permission"] for i in search_rel_res if i["permission"] != ""
            ]
            # 将租户套餐的菜单权限id列表与租户套餐信息组合
            result = dict(result)
            result["menu_ids"] = menu_ids
            result["menu_permission"] = menu_permission
            # 转换成pydantic的序列化模型
            result = TenantPackageAndMenuCompleteOutSchema.model_validate(result)
            if (not result.status) and raise_disabled_exception:
                # 禁用则报错
                raise generate_async_validator_exception("id", "租户套餐已被禁用")
        elif result is None and raise_not_exists_exception:
            # 未找到则报错
            raise generate_async_validator_exception("id", "租户套餐不存在")
        return result

    @data_cache(
        ttl="30m",
        key="complete_tenant_package_list:{search_schema}:{pagination_schema}:{order_schema}",
        tags=["complete_tenant_package_list"],
        condition=CashewsCondition.condition_only_true_result,
    )
    @staticmethod
    async def list_search_complete_tenant_package(
        db_session: AsyncSession,
        search_schema: TenantPackageListSearchValidateSchema,
        pagination_schema: PaginationSearchValidateSchema,
        order_schema: OrderBySearchValidateSchema,
        need_select_total: bool = True,
    ) -> tuple[list[TenantPackageCompleteOutSchema], int | None]:
        """完整信息的租户套餐列表查询

        Args:
            db_session (AsyncSession): 数据库连接池
            search_schema (TenantPackageListSearchValidateSchema): 查询参数
            pagination_schema: 分页参数
            order_schema: 排序参数
            need_select_total: 是否需要查询总数

        Returns:
            list[TenantPackageCompleteOutSchema]: 查询结果
        """
        # 构建排序字段
        order_by_field_sql = order_schema.convert_to_order_by_sql(
            search_schema, TenantPackage
        )
        # 构建查询sql
        list_search_sql = (
            select(
                *TenantPackageCompleteOutSchema.convert_to_sql_model_field(
                    TenantPackage
                )
            )
            .where(and_(*search_schema.convert_to_where_sql(TenantPackage)))
            .order_by(order_by_field_sql)
            .offset(pagination_schema.offset)
            .limit(pagination_schema.limit)
        )
        # 开始查询
        async with db_session.begin():
            list_search_res = await db_session.execute(list_search_sql)
        result = list_search_res.mappings().all()
        # 根据need_select_total参数来确定是否需要查询总数
        if need_select_total:
            total_sql = (
                select(func.count())
                .select_from(TenantPackage)
                .where(and_(*search_schema.convert_to_where_sql(TenantPackage)))
            )
            async with db_session.begin():
                total_res = await db_session.execute(total_sql)
            total = total_res.scalar()
        else:
            total = None
        # 统一转为pydantic列表模型
        return ListTenantPackageCompleteOutSchema.validate_python(result), total

    @data_cache(
        ttl="30m",
        key="simple_tenant_package_list:{search_schema}",
        tags=["simple_tenant_package_list"],
        condition=CashewsCondition.condition_only_true_result,
    )
    @staticmethod
    async def list_search_simple_tenant_package(
        db_session: AsyncSession,
        search_schema: TenantPackageListSearchValidateSchema,
    ) -> list[TenantPackageSimpleOutSchema]:
        """简单信息的租户套餐列表查询

        Args:
            db_session (AsyncSession): 数据库连接池
            search_schema (TenantPackageListSearchValidateSchema): 查询参数
            pagination_schema: 分页参数
            order_schema: 排序参数
            need_select_total: 是否需要查询总数

        Returns:
            list[ListTenantPackageCompleteOutSchema]: 查询结果
        """
        # 构建查询sql
        list_search_sql = select(
            *TenantPackageSimpleOutSchema.convert_to_sql_model_field(TenantPackage)
        ).where(and_(*search_schema.convert_to_where_sql(TenantPackage)))
        # 开始查询
        async with db_session.begin():
            list_search_res = await db_session.execute(list_search_sql)
        result = list_search_res.mappings().all()
        # 统一转为pydantic列表模型
        return ListTenantPackageSimpleOutSchema.validate_python(result)

    @data_cache(
        ttl="30m",
        key="tenant_package_all_menu_list:{tenant_package_id}",
        tags=[
            "tenant_package_all_menu_list",
            "tenant_package_all_menu_list:{tenant_package_id}",
        ],
        condition=CashewsCondition.condition_only_true_result,
    )
    @staticmethod
    async def list_tenant_package_all_menu(
        db_session: AsyncSession, tenant_package_id: int
    ) -> list[MenuCompleteOutSchema]:
        """获取租户套餐的所有菜单信息

        Args:
            db_session (AsyncSession): 数据库连接池
            tenant_package_id (int): 租户套餐ID

        Returns:
            list[MenuCompleteOutSchema]: 租户套餐的所有菜单信息
        """
        # 构建查询租户套餐的所有菜单ID的sql
        if tenant_package_id == 0:
            # 租户套餐ID为0表示系统级别租户, 获取所有菜单
            menu_ids_search_sql = select(Menu.id)
        else:
            menu_ids_search_sql = select(TenantPackageMenuRel.menu_id).where(
                TenantPackageMenuRel.tenant_package_id == tenant_package_id
            )
        # 开始查询
        async with db_session.begin():
            # 查询租户套餐的所有菜单ID
            menu_ids_search_sql_res = await db_session.execute(menu_ids_search_sql)
            menu_ids_search_sql_result = menu_ids_search_sql_res.scalars().all()
            # 没有菜单则直接返回空数组(不会缓存)
            if not menu_ids_search_sql_result:
                return []
            # 查询所有菜单ID对应的菜单信息
            menu_info_list_search_sql = (
                select(*MenuCompleteOutSchema.convert_to_sql_model_field(Menu))
                .where(Menu.id.in_(menu_ids_search_sql_result))
                .order_by(Menu.show_order.asc())
            )
            menu_info_list_search_res = await db_session.execute(
                menu_info_list_search_sql
            )
            menu_info_list_search_result = menu_info_list_search_res.mappings().all()
        # 统一转为pydantic列表模型
        return ListMenuCompleteOutSchema.validate_python(menu_info_list_search_result)

    @staticmethod
    @data_cache.transaction()
    async def create_tenant_package(
        db_session: AsyncSession,
        create_schema: TenantPackageCreateValidateSchema,
        creator_username: str = "",
    ) -> int:
        """创建租户套餐

        Args:
            db_session (AsyncSession): 数据库连接池
            create_info (TenantCreateValidateSchema): 创建信息
            creator_username: 创建者用户名

        Returns:
            int: 创建的sql主键ID
        """
        # 构建用户的创建信息字典(排除password参数)
        create_info_dict = create_schema.model_dump(exclude=("menu_ids",))
        # 创建sql
        create_sql = insert(TenantPackage).values(
            creator_username=creator_username,
            **create_info_dict,
        )
        async with db_session.begin():
            # 创建
            create_res = await db_session.execute(create_sql)
            created_id = create_res.inserted_primary_key_rows[0][0]
            # 添加租户套餐的菜单权限
            insert_batch_dict_list = [
                {
                    "tenant_package_id": created_id,
                    "menu_id": menu_id,
                }
                for menu_id in create_schema.menu_ids
            ]
            if insert_batch_dict_list:
                # 批量插入
                create_rel_sql = (insert(TenantPackageMenuRel), insert_batch_dict_list)
                await db_session.execute(*create_rel_sql)
            # 清空相关缓存标签
            await data_cache.delete_tags(
                "complete_tenant_package_list", "simple_tenant_package_list"
            )
        return created_id

    @staticmethod
    @data_cache.transaction()
    async def update_tenant_package(
        db_session: AsyncSession,
        update_schema: TenantPackageUpdateValidateSchema,
        updater_username: str = "",
    ):
        """修改租户套餐信息

        Args:
            db_session (AsyncSession): 数据库连接池
            update_schema (TenantPackageUpdateValidateSchema): 修改操作所需的数据
            updater_username (str, optional):修改者用户名
        """
        # 检查需修改的sql实例是否存在(禁用不报错)
        await TenantPackageService.get_tenant_package_use_id(
            db_session, update_schema.id, raise_disabled_exception=False
        )
        # 构建修改所需的数据(排除未设置的值和id)
        update_schema_data = update_schema.model_dump(
            exclude=("id", "menu_ids"), exclude_unset=True
        )
        if update_schema_data:
            # 修改sql
            update_sql = (
                update(TenantPackage)
                .values(**update_schema_data, updater_username=updater_username)
                .where(TenantPackage.id == update_schema.id)
            )
            # 删除租户套餐的菜单列表sql
            delete_rel_sql = delete(TenantPackageMenuRel).where(
                TenantPackageMenuRel.tenant_package_id == update_schema.id
            )
            # 添加租户套餐的菜单权限
            insert_batch_dict_list = [
                {
                    "tenant_package_id": update_schema.id,
                    "menu_id": menu_id,
                }
                for menu_id in update_schema.menu_ids
            ]
            # 修改操作
            async with db_session.begin():
                # 修改sql数据
                await db_session.execute(update_sql)
                # 删除租户套餐的菜单列表
                await db_session.execute(delete_rel_sql)
                # 插入租户套餐新的菜单列表
                if insert_batch_dict_list:
                    # 批量插入
                    create_rel_sql = (
                        insert(TenantPackageMenuRel),
                        insert_batch_dict_list,
                    )
                    await db_session.execute(*create_rel_sql)
                # 清空相关缓存标签
                await data_cache.delete_tags(
                    f"tenant_package#id:{update_schema.id}",
                    f"tenant_package_all_menu_list:{update_schema.id}",
                    "complete_tenant_package_list",
                    "simple_tenant_package_list",
                    "complete_tenant_list",  # 租户列表有用到租户套餐名称, 所以也清空掉
                )

    @staticmethod
    @data_cache.transaction()
    async def update_tenant_package_status(
        db_session: AsyncSession,
        update_schema: TenantPackageUpdateStatusValidateSchema,
        updater_username: str = "",
    ):
        """单独修改租户套餐状态

        Args:
            db_session (AsyncSession): 数据库连接池
            update_schema (TenantPackageUpdateStatusValidateSchema): 修改操作所需的数据
            updater_username (str, optional):修改者用户名
        """
        # 检查需修改的sql实例是否存在(禁用不报错)
        await TenantPackageService.get_tenant_package_use_id(
            db_session, update_schema.id, raise_disabled_exception=False
        )
        # 修改sql
        update_sql = (
            update(TenantPackage)
            .values(status=update_schema.status, updater_username=updater_username)
            .where(TenantPackage.id == update_schema.id)
        )
        # 修改操作
        async with db_session.begin():
            # 修改sql数据
            await db_session.execute(update_sql)
            # 清空相关缓存标签
            await data_cache.delete_tags(
                f"tenant_package#id:{update_schema.id}",
                "complete_tenant_package_list",
            )

    @staticmethod
    @data_cache.transaction()
    async def delete_tenant_package(
        db_session: AsyncSession,
        delete_schema: TenantPackageDeleteValidateSchema,
    ):
        """删除租户套餐

        Args:
            db_session (AsyncSession): 数据库连接池
            delete_schema (TenantPackageDeleteValidateSchema): 删除操作所属的数据
        """
        # 查找需删除的租户套餐
        # 构建查询sql
        select_sql = select(TenantPackage.id).where(
            TenantPackage.id.in_(delete_schema.ids)
        )
        # 进行sql查询
        async with db_session.begin():
            select_res = await db_session.execute(select_sql)
        select_result = select_res.mappings().all()
        # 获取需要删除的租户套餐id列表
        delete_id_list = [i["id"] for i in select_result]
        # 检查租户套餐是否有关联的租户
        select_tenant_sql = select(Tenant.id).where(
            Tenant.tenant_package_id.in_(delete_id_list)
        )
        # 进行sql查询
        async with db_session.begin():
            select_tenant_res = await db_session.execute(select_tenant_sql)
        select_tenant_result = select_tenant_res.scalar()
        if select_tenant_result is not None:
            # 租户套餐有关联的租户时不允许删除
            raise generate_async_validator_exception(
                "ids", "不允许删除有关联租户的租户套餐"
            )

        # 进行删除操作
        if select_result:
            # 删除租户套餐本身的sql
            delete_sql = delete(TenantPackage).where(
                TenantPackage.id.in_(delete_id_list)
            )
            # 删除租户套餐的相关菜单权限sql
            delete_rel_sql = delete(TenantPackageMenuRel).where(
                TenantPackageMenuRel.tenant_package_id.in_(delete_id_list)
            )
            # 删除操作
            async with db_session.begin():
                # 删除sql中的数据
                await db_session.execute(delete_sql)
                await db_session.execute(delete_rel_sql)
                # 清空相关缓存标签
                await data_cache.delete_tags(
                    *[f"tenant_package#id:{i}" for i in delete_id_list],
                    *[f"tenant_package_all_menu_list:{i}" for i in delete_id_list],
                    "complete_tenant_package_list",
                    "simple_tenant_package_list",
                )
